using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Threading;

namespace SqlTools.DataAccessLibrary
{
    public static class DBUtil
    {
        private static string _ConnectionName="";
        private static string _ConnectionString="";
        public static string ConnectionName()
        {
            return _ConnectionName;
        }
        public static string ConnectionString()
        {
            return _ConnectionString;
        }

        // call prepare connection prior to using generated sql commands/data readers
        public static void  Initialize(string ConnName)
        {
            LookupConnectionString(ConnName);
            
        }
        public static SqlConnection  PrepareConnection(string ConnName)
        {
            // factory for one connection at a time
            SqlConnection conn = null; 
            try
            {
                conn = new SqlConnection(_ConnectionString);
                conn.Open();
              
            }
            catch
            {
                conn = null;
            }
            return conn;
        }

        private static void LookupConnectionString(string ConnName)
        {
            if (string.IsNullOrEmpty(ConnName))
            {
                throw new Exception("DBUtil.GetConnectionString ConnName may not be blank");
            }
            if (_ConnectionName != ConnName)
            {
                _ConnectionName = ConnName;
                lock (_ConnectionString)
                {
                    _ConnectionString = "";
                    try
                    {
                        if (ConfigurationManager.ConnectionStrings[ConnName] != null)
                        {
                            _ConnectionString = ConfigurationManager.ConnectionStrings[ConnName].ConnectionString;
                        }
                    }
                    finally
                    {
                    }
                }
            }
        }
        // user must remember to close the reader after consumption to close the connection
        public static bool PrepareCommandForExecution(SqlCommand cmd, bool Transactionalize)
        {
            bool status = true;
            try
            {
                SqlConnection conn = PrepareConnection(_ConnectionName);
                cmd.Connection = conn;
                SqlTransaction trans = null;
                cmd.Transaction = trans;
                if (Transactionalize)
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                status = true;
            }
            catch
            {
                status = false;
            }
            return status;
        }
        public static SqlDataReader GetDataReader(SqlCommand cmd)
        {
            SqlDataReader reader = null;
            if (PrepareCommandForExecution(cmd, false))
                reader = cmd.ExecuteReader();
            return reader;
        }
        public static SqlCommand PrepareSqlCommand(string SqlString, bool Transactionalize)
        {
            SqlCommand cmd = null;

            if(string.IsNullOrEmpty(_ConnectionName))
            {
                throw new Exception("Initialize must be called");
            }
            cmd = new SqlCommand(SqlString);
            PrepareCommandForExecution(cmd, Transactionalize);
            return cmd;
        }
    }
}
